﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Data.SqlClient;
using System.Data.OleDb;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class ProjectRequestExpenseDAL : IProjectRequestExpenseDAL
    {
       
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ProjectRequestExpenseDAL()
        {

        }

        public void Insert(ProjectExpenseInfo pj)
        {
            //string strSQL;

            //try
            //{
            //    dbManager.Open();
            //    dbManager.CreateParameters(4);

            //    dbManager.AddParameters(0, "@reqSeq", reqSeq);
            //    dbManager.AddParameters(1, "@exp", exp);
            //    dbManager.AddParameters(2, "@status", SystemInfo._WorkRecordStatus);
            //    dbManager.AddParameters(3, "@user", user);


            //    strSQL = "INSERT INTO BGM_REQUEST_EXPENSE(BGM_REQ_SEQ, BGM_EXP_SEQ, BGM_SOURCE_SEQ, RECORD_STATUS, CREATED_BY)"
            //        + " VALUES(@reqSeq, @exp, (select eg.bgm_source_seq from bgm_mas_expense e inner join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq where e.bgm_exp_seq = @exp), @status, @user)";

            //    return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            //}
            //catch
            //{
            //    return 0;
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
        }

        public void Update(ProjectExpenseInfo pj)
        {
            //string strSQL;

            //try
            //{
            //    dbManager.Open();
            //    dbManager.CreateParameters(9);

            //    dbManager.AddParameters(0, "@reqExpSeq", reqExpSeq);
            //    dbManager.AddParameters(1, "@reqExpDescr", reqExpDescr);
            //    dbManager.AddParameters(2, "@reqExpPrice", reqExpPrice);
            //    dbManager.AddParameters(3, "@reqExpQty", reqExpQty);
            //    dbManager.AddParameters(4, "@reqExpMtef1", reqExpMtef1);
            //    dbManager.AddParameters(5, "@reqExpMtef2", reqExpMtef2);
            //    dbManager.AddParameters(6, "@reqExpMtef3", reqExpMtef3);
            //    dbManager.AddParameters(7, "@reqExpResource", reqExpResource);
            //    dbManager.AddParameters(8, "@status", SystemInfo._WorkRecordStatus);

            //    strSQL = "UPDATE BGM_REQUEST_EXPENSE"
            //        + " SET BGM_REQ_EXP_DESCR = @reqExpDescr"
            //            + ", BGM_REQ_EXP_PRICE = @reqExpPrice"
            //            + ", BGM_REQ_EXP_QTY = @reqExpQty"
            //            + ", BGM_REQ_EXP_MTEF1 = @reqExpMtef1"
            //            + ", BGM_REQ_EXP_MTEF2 = @reqExpMtef2"
            //            + ", BGM_REQ_EXP_MTEF3 = @reqExpMtef3"
            //            + ", ICM_RESOURCE_LIST_SEQ = @reqExpResource"
            //            + ", RECORD_STATUS = @status"
            //            + ", UPDATED_WHEn = GetDate()"
            //        + " WHERE BGM_REQ_EXP_SEQ = @reqExpSeq";

            //    return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            //}
            //catch
            //{
            //    return 0;
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
        }

        public void Delete(ProjectExpenseInfo pj)
        {
            //string strSQL;

            //try
            //{
            //    dbManager.Open();
            //    dbManager.CreateParameters(3);

            //    dbManager.AddParameters(0, "@seq", seq);
            //    dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
            //    dbManager.AddParameters(2, "@updated_by", updated_by);

            //    strSQL = "UPDATE BGM_REQUEST_EXPENSE"
            //        + " SET RECORD_STATUS = @record_status"
            //            + ", UPDATED_BY = @updated_by"
            //            + ", UPDATED_WHEN = GetDate()"
            //        + " WHERE BGM_REQ_EXP_SEQ = @seq";

            //    return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            //}
            //catch
            //{
            //    return 0;
            //}
            //finally
            //{
            //    dbManager.Dispose();
            //}
        }

        public IList<ProjectExpenseInfo> Search(ProjectExpenseInfo pj)
        {
            IList<ProjectExpenseInfo> pjInfo = new List<ProjectExpenseInfo>();

            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@seq", pj.Sequence);
                dbManager.AddParameters(1, "@pjSeq", pj.Project.Sequence);
                dbManager.AddParameters(2, "@srcSeq", pj.Source.Sequence);
                dbManager.AddParameters(3, "@expSeq", pj.Expense.Sequence);
                dbManager.AddParameters(4, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT BGM_REQ_EXP_SEQ, BGM_REQ_SEQ, DESCR, TOTAL_AMOUNT"
                        + ", COALESCE(UNIT_QTY, 1), REMARK"
                        + ", MTEF1, MTEF2, MTEF3"
                        + ", BGM_SOURCE_SEQ, BGM_EXP_SEQ"
                        + ", ICM_RESOURCE_TYPE, ICM_RESOURCE_SEQ"
                    + " FROM BGM_REQUEST_EXPENSE"
                    + " WHERE 0=0"
                        + " AND BGM_REQ_SEQ = @pjSeq"
                        + ((pj.Sequence > 0) ? " AND BGM_REQ_EXP_SEQ = @seq" : "")
                        + ((pj.Source.Sequence > 0) ? " AND BGM_SOURCE_SEQ = @srcSeq" : "")
                        + ((pj.Expense.Sequence > 0) ? " AND BGM_EXP_SEQ = @expSeq" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    ProjectExpenseInfo req = new ProjectExpenseInfo();
                    // ------------------------------- Source DAL -------------------------------------

                    SourceDAL srcCtrl = new SourceDAL();
                    IList<SourceInfo> srcInfo = new List<SourceInfo>();

                    var tempSource = Convert.ToInt32(dr["BGM_SOURCE_SEQ"]);
                    srcInfo = srcCtrl.Search(new SourceInfo(tempSource, "", "", "", "", null, null, null, null));

                    //-------------------------------- Expense DAL ------------------------------------

                    ExpenseDAL expCtrl = new ExpenseDAL();
                    IList<ExpenseInfo> expInfo = new List<ExpenseInfo>();
                    Int32 tempExpense = Convert.ToInt32(dr["BGM_EXP_SEQ"]);
                    expInfo = expCtrl.Search(new ExpenseInfo(tempExpense, "", "", "", "", new ExpenseInfo(0), null, null, null, null, null, null));
                   
                    //-------------------------------- Expense Group DAL ------------------------------------

                    ExpenseGroupDAL expGrpCtrl = new ExpenseGroupDAL();
                    IList<ExpenseGroupInfo> expGrpInfo = new List<ExpenseGroupInfo>();
                    Int32 tempExpenseGroup = Convert.ToInt32(expInfo[0].ExpenseGroup.Sequence);
                    expGrpInfo = expGrpCtrl.Search(new ExpenseGroupInfo(tempExpenseGroup, "", "", "", "", null, null, null, null));

                    expInfo[0].ExpenseGroup = expGrpInfo[0];

                    //    // -------------------------------- Project DAL ------------------------------------

                    ProjectRequestDAL pjCtrl = new ProjectRequestDAL();
                    ProjectInfo proj = new ProjectInfo();
                    IList<ProjectInfo> pjList = new List<ProjectInfo>();

                    //Int32 temp = Convert.ToInt32(GetInt32(1));
                    //proj.Sequence =  rdr.GetInt32(1);
                    //pjList = pjCtrl.Search(proj);

                    ////    // ------------------------------------------------------------------------------------

                    //req.Sequence = rdr.GetInt32(0);
                    //req.Project = pjList[0];
                    //req.Description = (rdr.IsDBNull(2)) ? "" : rdr.GetString(2);
                    //req.Price = rdr.GetDouble(3);
                    //req.Quantity = (rdr.IsDBNull(4)) ? null : (Nullable<int>)rdr.GetInt32(4);
                    //req.Remark = (rdr.IsDBNull(5)) ? "" : rdr.GetString(6);
                    //req.MTEF1 = (rdr.IsDBNull(6)) ? null : (Nullable<int>)rdr.GetInt32(7);
                    //req.MTEF2 = (rdr.IsDBNull(7)) ? null : (Nullable<int>)rdr.GetInt32(8);
                    //req.MTEF3 = (rdr.IsDBNull(8)) ? null : (Nullable<int>)rdr.GetInt32(9);
                    //req.Source = srcInfo[0];
                    //req.Expense = expInfo[0];

                   pjInfo.Add(req);

                 }
                //while (rdr.Read())
                //{
                //    ProjectExpenseInfo req = new ProjectExpenseInfo();

                //    // ------------------------------- Source DAL -------------------------------------

                //    SourceDAL srcCtrl = new SourceDAL();
                //    IList<SourceInfo> srcInfo = new List<SourceInfo>();

                //    srcInfo = srcCtrl.Search(new SourceInfo(rdr.GetInt32(9), "", "", "", "", null, null, null, null));

                //    //-------------------------------- Expense DAL ------------------------------------

                //    ExpenseDAL expCtrl = new ExpenseDAL();
                //    IList<ExpenseInfo> expInfo = new List<ExpenseInfo>();

                //    expInfo = expCtrl.Search(new ExpenseInfo(rdr.GetInt32(10), "", "", "", "", new ExpenseInfo(0), null, null, null, null, null, null));

                //    //-------------------------------- Expense Group DAL ------------------------------------

                //    ExpenseGroupDAL expGrpCtrl = new ExpenseGroupDAL();
                //    IList<ExpenseGroupInfo> expGrpInfo = new List<ExpenseGroupInfo>();

                //    expGrpInfo = expGrpCtrl.Search(new ExpenseGroupInfo(expInfo[0].ExpenseGroup.Sequence, "", "", "", "", null, null, null, null));

                //    expInfo[0].ExpenseGroup = expGrpInfo[0];

                //    // -------------------------------- Project DAL ------------------------------------

                //    ProjectRequestDAL pjCtrl = new ProjectRequestDAL();
                //    ProjectInfo proj = new ProjectInfo();
                //    IList<ProjectInfo> pjList = new List<ProjectInfo>();

                //    proj.Sequence = rdr.GetInt32(1);
                //    pjList = pjCtrl.Search(proj);

                //    // ------------------------------------------------------------------------------------

                //    req.Sequence = rdr.GetInt32(0);
                //    req.Project = pjList[0];
                //    req.Description = (rdr.IsDBNull(2)) ? "" : rdr.GetString(2);
                //    req.Price = rdr.GetDouble(3);
                //    req.Quantity = (rdr.IsDBNull(4)) ? null : (Nullable<int>)rdr.GetInt32(4);
                //    req.Remark = (rdr.IsDBNull(5)) ? "" : rdr.GetString(6);
                //    req.MTEF1 = (rdr.IsDBNull(6)) ? null : (Nullable<int>)rdr.GetInt32(7);
                //    req.MTEF2 = (rdr.IsDBNull(7)) ? null : (Nullable<int>)rdr.GetInt32(8);
                //    req.MTEF3 = (rdr.IsDBNull(8)) ? null : (Nullable<int>)rdr.GetInt32(9);
                //    req.Source = srcInfo[0];
                //    req.Expense = expInfo[0];

                //    pjInfo.Add(req);
                //}

                return pjInfo;
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
